-- =============================================
-- This procedure gets all the menu items to display on the admin side.
--
-- MODIFICATION HISTORY
--
-- 01/03/2001      N.Malhotra    Simplified joins in sub-selects for DescendantCount and AuthorizedDescendantCount for Performance Reasons
-- 02/23/2002   N.Malhotra    Increased Max Sort Value to 999999999
-- 02/27/2003   R.Wenger    Added NOLOCKs to fix problems deadlocking with massive concurrency
-- 08/01/2003   E.Tatsui    Added sub query for error count. Added logic to filter out by website key.
-- 10/10/2003   E.Tatsui    Added super user logic.
-- 09/26/2005   E.Tatsui    Updated super user logic.
-- =============================================

CREATE                                  procedure amsp_CMGetMenuItems
    @ContactID numeric,
    @NavContentGroupInd char(1),
  @WebsiteKey uniqueidentifier = NULL
as
BEGIN

  DECLARE
    @AdminWebsiteKey uniqueidentifier,
    @SuperGroupID numeric

  -- Find out if this user is a member of a super group.
  SELECT @SuperGroupID = a.ContentAuthorityGroupID
    FROM Content_Authority_Group a, Content_Authority_Producer b
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @ContactID
     AND a.SuperGroupFlag = 'Y'

  SELECT DISTINCT a.*,
         b.ContentEditorFlag,
         b.ContentApproverFlag,
         b.NavCreatorFlag,
         b.NavEditorFlag,
         b.CustomPageFlag,
         b.LayoutFlag,
         b.UploadFlag,
         b.EditorFlag,
         b.ComponentScriptFlag,
         c.HideFlag AS ParentHideFlag,
        (SELECT count(*)
           FROM Nav_Menu z WITH (NOLOCK)
          WHERE (z.NavContentGroupInd = 'C' OR z.WebsiteKey = a.WebsiteKey)
            AND z.NavContentGroupInd = a.NavContentGroupInd
            AND z.WorkflowStatusCode <> 'D'
            AND z.SortOrder > a.SortOrder
            AND z.SortOrder <
               (SELECT IsNull(Min(x.SortOrder),999999999)
                  FROM Nav_Menu x
                   WITH (NOLOCK)
                 WHERE x.SortOrder > a.SortOrder
                   AND x.CategoryDepth <= a.CategoryDepth
                   AND (x.NavContentGroupInd = 'C' OR x.WebsiteKey = a.WebsiteKey)
                   AND x.NavContentGroupInd = a.NavContentGroupInd)
            AND (@SuperGroupID IS NOT NULL
             OR z.ContentAuthorityGroupID IN (SELECT ContentAuthorityGroupID
                                                            FROM Content_Authority_Producer WITH (NOLOCK)
                                                           WHERE ContactID = @ContactID))) AS AuthorizedDescendantCount,
         NULL As AncestoryHideFlag,
         (SELECT count(*)
            FROM Content z
             WITH (NOLOCK)
           WHERE z.NavMenuID = a.NavMenuID
             AND z.WorkflowStatusCode = 'W') AS WorkingContentCount,
         (SELECT count(*)
            FROM Content z
             WITH (NOLOCK)
          WHERE z.NavMenuID = a.NavMenuID
             AND (z.WorkflowStatusCode = 'Q' OR z.WorkflowStatusCode = 'E')) AS PendingContentCount,
         (SELECT count(*)
            FROM Content z
            WITH (NOLOCK)
           WHERE z.NavMenuID = a.NavMenuID
             AND z.WorkflowStatusCode = 'A') AS ApprovedContentCount,
         (SELECT Count(ContentID)
            FROM vCurrent_Content z
             WITH (NOLOCK)
           WHERE z.NavMenuID = a.NavMenuID) AS CurrentContentCount,
         (SELECT MAX(ContentID)
            FROM vCurrent_Content z
             WITH (NOLOCK)
           WHERE z.NavMenuID = a.NavMenuID
             AND (z.ContentID = a.ContentID OR z.PreviousContentID = a.ContentID)) AS WorkingContentID,
         (SELECT Count(*)
            FROM Content z WITH (NOLOCK), Publish_Request_Detail y WITH (NOLOCK), Publish_Message_Log x WITH (NOLOCK)
           WHERE z.ContentID = y.ContentID
             AND y.PublishRequestDetailID = x.PublishRequestDetailID
             AND y.PublishRequestStatusCode = 'F'
             AND z.NavMenuID = a.NavMenuID
             AND x.DisplayFlag = 'Y') AS ErrorCount
    INTO #Temp
    FROM (Nav_Menu a WITH (NOLOCK) LEFT OUTER JOIN Content_Authority_Producer b
     WITH (NOLOCK)
      ON (@SuperGroupID IS NULL AND a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
            AND b.ContactID = @ContactID)
      OR (@SuperGroupID IS NOT NULL AND b.ContentAuthorityGroupID = @SuperGroupID
             AND b.ContactID = @ContactID))
     LEFT OUTER JOIN Nav_Menu c WITH (NOLOCK)
    ON a.ParentNavMenuID = c.NavMenuID
   WHERE a.NavContentGroupInd = @NavContentGroupInd
     AND (a.MicrositeFlag IS NULL OR a.MicrositeFlag = 'N')
     AND (a.WebsiteKey = CASE WHEN @NavContentGroupInd = 'N' THEN @WebsiteKey
                            ELSE a.WebsiteKey END
      OR a.WebsiteKey IS NULL)
     AND a.WorkflowStatusCode <> 'D'
   ORDER BY a.SortOrder

        
  SELECT @AdminWebsiteKey = Value
    FROM System_Variable WITH (NOLOCK)
   WHERE Name = 'CMAdminWebsiteKey'
     

  -- For Admin site, only show licensed components.
  IF @WebsiteKey = @AdminWebsiteKey BEGIN
    DECLARE
      @NavMenuID numeric,
      @ParentNavMenuID numeric,
      @AncestorNavMenuID numeric,
      @LoopID numeric

    DECLARE c_Inactives CURSOR FOR
    SELECT NavMenuID,
           ParentNavMenuID,
           AncestorNavMenuID
      FROM #Temp
     WHERE ComponentCode IS NOT NULL
       AND NOT EXISTS(SELECT 1
                        FROM Component_Ref x WITH (NOLOCK)
                       WHERE #Temp.ComponentCode = x.ComponentCode
                         AND x.ActiveFlag = 'Y')
     ORDER BY SortOrder

    OPEN c_Inactives
    FETCH NEXT FROM c_Inactives
          INTO @NavMenuID,
               @ParentNavMenuID,
               @AncestorNavMenuID

    WHILE @@FETCH_STATUS = 0 BEGIN
      SET @LoopID = @ParentNavMenuID
      -- Decrement DescendantCount for all the ancestors.
      WHILE @LoopID IS NOT NULL OR @LoopID <> @AncestorNavMenuID BEGIN
        UPDATE #Temp
           SET DescendantCount = DescendantCount -1
         WHERE NavMenuID = @LoopID

        SELECT @LoopID = ParentNavMenuID
          FROM #Temp
         WHERE NavMenuID = @LoopID

        IF @LoopID IS NULL OR @@RowCount = 0
          BREAK
      END

      DELETE FROM #Temp
       WHERE NavMenuID = @NavMenuID

      FETCH NEXT FROM c_Inactives
            INTO @NavMenuID,
                 @ParentNavMenuID,
                 @AncestorNavMenuID
    END
    CLOSE c_Inactives
    DEALLOCATE c_Inactives
  END

  SELECT a.*,
         CASE WHEN c.ContentID IS NOT NULL THEN 'Y'
         ELSE 'N' END AS HTMLContentFlag
    FROM #Temp a LEFT OUTER JOIN Content b  WITH (NOLOCK)
      ON a.WorkingContentID = b.ContentID
    LEFT OUTER JOIN Content_HTML c  WITH (NOLOCK)
      ON b.ContentID = c.ContentID
   ORDER BY a.SortOrder

END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetMenuItems] TO [IMIS]
GO